package cn.lili.modules.member.mapper;

import cn.lili.modules.member.entity.dos.Extension;
import cn.lili.modules.member.entity.dos.Member;
import cn.lili.modules.member.entity.vo.ExtensionVO;
import cn.lili.modules.member.entity.vo.MemberVO;
import cn.lili.modules.member.entity.vo.PartnerVO;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * @author Bulbasaur
 * @since 2020-02-25 14:10:16
 */
public interface ExtensionMapper extends BaseMapper<Extension> {

	@Select("select a.*,b.end_time MemberEndTime,c.username,c.nick_name,c.mobile,c.face,c.have_store,"
			+ "s.store_logo,s.store_name,s.store_address_path,s.store_address_detail,r.name locationName,c.create_time createTime "
			+ "from li_extension a" + " left join li_member c on a.member_id = c.id "
			+ " LEFT JOIN li_grade_level b ON c.id = b.member_id" + " left join li_region r on c.location = r.ad_code "
			+ " left join li_partner p on p.member_id = c.id AND p.delete_flag = '0' AND p.partner_state = '0'"
			+ " left join li_store s on s.member_id = c.id ${ew.customSqlSegment}")
	IPage<ExtensionVO> pageByMemberVO(IPage<Extension> page, @Param(Constants.WRAPPER) Wrapper<Extension> queryWrapper);

	@Select("SELECT DISTINCT( c.mobile ) AS mobile,r.pay_status,m.`name`,c.username,c.nick_name,c.real_name,c.face,c.have_store,s.store_logo,s.store_name,\n"
			+ "s.store_address_path,s.store_address_detail ,DATE_ADD(a.create_time,INTERVAL (select item_value from sys_dict_item where dict_id = '042a2f375a37798006d979393e2bb03c') DAY) as end_time "
			+ "FROM li_extension a " + "LEFT JOIN li_member c ON a.member_id = c.id "
			+ "LEFT JOIN li_partner p ON p.member_id = c.id AND p.delete_flag = '0' AND p.partner_state = '0' "
			+ "LEFT JOIN li_store s ON s.member_id = c.id "
			+ "LEFT JOIN li_grade_level g on g.member_id = a.member_id  and g.delete_flag = 0 and g.grade_state = 0 AND g.end_time > now() "
			+ "LEFT JOIN li_membership_card m on m.id = g.grade_id  and m.`level` in (1, 2) "
			+ "LEFT JOIN li_recharge r on r.member_id = a.member_id and r.pay_status ='PAID' ${ew.customSqlSegment}")
	IPage<ExtensionVO> getByPageCode(IPage<Extension> page, @Param(Constants.WRAPPER) Wrapper<Extension> queryWrapper);

	@Select("select promotion_code from li_member  ${ew.customSqlSegment}")
	String queryBusinessOrder(@Param(Constants.WRAPPER) Wrapper<Member> queryWrapper);

	// 根据会员id查看推广人信息
	@Select("select DISTINCT m.*,r.name as partnerName from li_extension e left join li_member m on e.extension = m.promotion_code "
			+ "left join li_partner p on p.member_id = m.id  and p.partner_state = 0 and p.delete_flag = 0 and NOW() < DATE_FORMAT(p.end_time,'%Y-%m-%d') "
			+ "left join li_role r on r.id = p.role_id ${ew.customSqlSegment}")
	MemberVO findByMemberId(@Param(Constants.WRAPPER) Wrapper<Extension> queryWrapper);

	// 根据会员id查看推广人信息
	@Select("select DISTINCT m.*,r.name as partnerName from li_member m "
			+ "left join li_partner p on p.member_id = m.id  and p.partner_state = 0 and p.delete_flag = 0 and NOW() < DATE_FORMAT(p.end_time,'%Y-%m-%d') "
			+ "left join li_role r on r.id = p.role_id ${ew.customSqlSegment}")
	MemberVO findByPromotionCode(@Param(Constants.WRAPPER) Wrapper<Extension> queryWrapper);

	// 根据会员id修改状态
	@Update("UPDATE li_extension set delete_flag = 1 where member_id = #{memberId}")
	boolean updateByMemberId(String memberId);

	@Select("<script> SELECT e.* FROM li_extension e " + "WHERE e.delete_flag = 0 "
			+ "<if test=\"extension!=null and extension!=''\"> AND e.extension = #{extension} </if> "
			+ "and DATE_FORMAT( NOW(), '%Y-%m-%d 23:59:59') >= DATE_ADD(e.create_time,INTERVAL "
			+ " (select item_value from sys_dict_item where dict_id = '042a2f375a37798006d979393e2bb03c')-3 DAY ) "
			+ "AND e.member_id NOT IN ( SELECT e.member_id FROM li_extension e\n"
			+ " LEFT JOIN li_wallet_detail d ON d.payer = e.member_id WHERE  e.delete_flag = 0 "
			+ "<if test=\"extension!=null and extension!=''\"> AND e.extension = #{extension} </if> ) " + "</script>")
	List<Extension> getUnbindCount(@Param("extension") String extension);

	@Select("<script> SELECT e.* FROM li_extension e " + "WHERE e.delete_flag = 0 "
			+ "<if test=\"extension!=null and extension!=''\"> AND e.extension = #{extension} </if> "
			+ "and DATE_FORMAT( NOW(), '%Y-%m-%d 23:59:59') >= DATE_ADD(e.create_time,INTERVAL "
			+ " (select item_value from sys_dict_item where dict_id = '042a2f375a37798006d979393e2bb03c') DAY ) "
			+ "AND e.member_id NOT IN ( SELECT e.member_id FROM li_extension e\n"
			+ " LEFT JOIN li_wallet_detail d ON d.payer = e.member_id WHERE  e.delete_flag = 0 "
			+ "<if test=\"extension!=null and extension!=''\"> AND e.extension = #{extension} </if> ) " + "</script>")
	List<Extension> updateUnbind(@Param("extension") String extension);

	@Select("SELECT concat(p.prefix,LPAD(code,5,0))as prefixCode ,m.nick_name as username,p.* FROM li_partner p LEFT JOIN li_member m on m.id = p.member_id "
			+ "WHERE p.member_id =#{memberId} and p.delete_flag = 0 and m.delete_flag = 0 ")
	PartnerVO getCertificate(@Param("memberId") String memberId);

	// 根据推广码修改状态
	@Update(" UPDATE li_extension a LEFT JOIN li_member b on  a.member_id = b.id "
			+ " SET a.delete_flag = 1 ,b.extension_flag = 1 " + " WHERE " + " a.extension = #{extensionCode}"
			+ " AND a.delete_flag = 0 ")
	boolean updateByExtensionCode(String extensionCode);

	@Select("SELECT DISTINCT (c.mobile) AS mobile, r.pay_status, m.`name`, c.username, c.nick_name, c.real_name, c.face, "
			+ "c.have_store, s.store_logo, s.store_name, s.store_address_path, s.store_address_detail, "
			+ "DATE_ADD(c.create_time, INTERVAL (select item_value from sys_dict_item where dict_id = '042a2f375a37798006d979393e2bb03c') DAY) AS end_time "
			+ " from li_member c LEFT JOIN li_partner p ON p.member_id = c.id AND p.delete_flag = '0' AND p.partner_state = '0' "
			+ " LEFT JOIN li_store s ON s.member_id = c.id "
			+ " LEFT JOIN li_grade_level g ON g.member_id = c.id AND g.delete_flag = 0 AND g.grade_state = 0 AND g.end_time > now()  "
			+ " LEFT JOIN li_membership_card m ON m.id = g.grade_id AND m.`level` IN (1, 2) "
			+ " LEFT JOIN li_recharge r ON r.member_id = c.id AND r.pay_status = 'PAID' ${ew.customSqlSegment}")
	IPage<ExtensionVO> getByPageMember(IPage<Extension> page,
			@Param(Constants.WRAPPER) Wrapper<Extension> queryWrapper);

}